# 使用sqlite3
import sqlite3
from typing import get_type_hints

class Database:
    def __init__(self, db_name):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()

    def create_table_from_class(self, cls):
        """
        根据类定义动态创建表
        """
        hints = get_type_hints(cls)
        columns = []
        columns.append("id INTEGER PRIMARY KEY")
        for col_name, col_type in hints.items():
            # 将Python类型映射到SQLite类型
            sqlite_type = 'TEXT'  # 默认类型
            if col_type == int:
                sqlite_type = 'INTEGER'
            elif col_type == float:
                sqlite_type = 'REAL'
            elif col_type == str:
                sqlite_type = 'TEXT'
            columns.append(f"{col_name} {sqlite_type}")
        columns_str = ", ".join(columns)
        sql = f"CREATE TABLE IF NOT EXISTS {cls.__name__} ({columns_str})"
        self.cursor.execute(sql)
        self.conn.commit()

    def insert_from_object(self, obj):
        """
        根据对象属性插入数据
        """
        columns = ', '.join(obj.__dict__.keys())
        placeholders = ', '.join(['?' for _ in obj.__dict__.values()])
        sql = f"INSERT INTO {obj.__class__.__name__} ({columns}) VALUES ({placeholders})"
        self.cursor.execute(sql, tuple(obj.__dict__.values()))
        self.conn.commit()

    def update_from_object(self, obj, condition):
        """
        根据对象属性更新数据
        """
        updates = ', '.join([f"{col} = ?" for col in obj.__dict__.keys()])
        condition_str = ' AND '.join([f"{k} = ?" for k in condition.keys()])
        sql = f"UPDATE {obj.__class__.__name__} SET {updates} WHERE {condition_str}"
        self.cursor.execute(sql, tuple(obj.__dict__.values()) + tuple(condition.values()))
        self.conn.commit()

    def delete(self, table_name, condition):
        """
        删除数据
        """
        condition_str = ' AND '.join([f"{k} = ?" for k in condition.keys()])
        sql = f"DELETE FROM {table_name} WHERE {condition_str}"
        self.cursor.execute(sql, tuple(condition.values()))
        self.conn.commit()

    def select(self, table_name, columns='*'):
        """
        查询数据
        """
        sql = f"SELECT {columns} FROM {table_name}"
        self.cursor.execute(sql)
        return [dict(zip([column[0] for column in self.cursor.description], row)) for row in self.cursor.fetchall()]
    
    def get_table_length(self, table_name):
        """
        返回指定表的行数
        :param table_name: 表名
        :return: 表的行数
        """
        try:
            self.cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
            return self.cursor.fetchone()[0]
        except sqlite3.OperationalError as e:
            print(f"Error: {e}. Table '{table_name}' does not exist or the name is incorrect.")
            return None


    def __del__(self):
        self.conn.close()

# 定义一个类作为示例
class User:
    id: int
    name: str
    age: int



# 使用示例
if __name__ == "__main__":
    db = Database('example.db')

    # 动态创建表
    db.create_table_from_class(User)

    # 插入数据
    user = User()
    user.id = 1
    user.name = 'Alice'
    user.age = 30
    db.insert_from_object(user)

    # 更新数据
    user.name = 'Alicia'
    user.age = 31
    db.update_from_object(user, {'id': 1})

    # 查询数据
    users = db.select('User')
    print(users)

    # 删除数据
    db.delete('User', {'id': 1})